﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Aliyeye.Car.Net.Utility;

namespace Aliyeye.Car.Net.Dal
{
    public class Illegal
    {
        public Illegal()
        { }
        public int Add(Model.Illegal model)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4),
					new SqlParameter("@CarCode", SqlDbType.NVarChar,50),
					new SqlParameter("@Time", SqlDbType.DateTime),
					new SqlParameter("@Addr", SqlDbType.NVarChar,300),
					new SqlParameter("@MeiTi", SqlDbType.NVarChar,50),
					new SqlParameter("@Type", SqlDbType.NVarChar,50),
					new SqlParameter("@Price", SqlDbType.Decimal,9),
					new SqlParameter("@Marks", SqlDbType.NVarChar,500),
					new SqlParameter("@UserId", SqlDbType.Int,4),
					new SqlParameter("@Inputime", SqlDbType.DateTime),
					new SqlParameter("@Hours", SqlDbType.NVarChar,50),
					new SqlParameter("@Driver", SqlDbType.NVarChar,50),
					new SqlParameter("@Cents", SqlDbType.NVarChar,300)};
            parameters[0].Direction = ParameterDirection.Output;
            parameters[1].Value = model.CarCode;
            parameters[2].Value = model.Time;
            parameters[3].Value = model.Addr;
            parameters[4].Value = model.MeiTi;
            parameters[5].Value = model.Type;
            parameters[6].Value = model.Price;
            parameters[7].Value = model.Marks;
            parameters[8].Value = model.UserId;
            parameters[9].Value = model.Inputime;
            parameters[10].Value = model.Hours;
            parameters[11].Value = model.Driver;
            parameters[12].Value = model.Cents;
            int roleId = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "Illegal_ADD", parameters);
            if (roleId > 0)
            {
                return roleId;
            }
            else
            {
                return 0;
            }
        }
        public bool Update(Model.Illegal model)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4),
					new SqlParameter("@CarCode", SqlDbType.NVarChar,50),
					new SqlParameter("@Time", SqlDbType.DateTime),
					new SqlParameter("@Addr", SqlDbType.NVarChar,300),
					new SqlParameter("@MeiTi", SqlDbType.NVarChar,50),
					new SqlParameter("@Type", SqlDbType.NVarChar,50),
					new SqlParameter("@Price", SqlDbType.Decimal,9),
					new SqlParameter("@Marks", SqlDbType.NVarChar,500),
					new SqlParameter("@UserId", SqlDbType.Int,4),
					new SqlParameter("@Inputime", SqlDbType.DateTime),
					new SqlParameter("@Hours", SqlDbType.NVarChar,50),
					new SqlParameter("@Driver", SqlDbType.NVarChar,50),
					new SqlParameter("@Cents", SqlDbType.NVarChar,300)};
            parameters[0].Value = model.Id;
            parameters[1].Value = model.CarCode;
            parameters[2].Value = model.Time;
            parameters[3].Value = model.Addr;
            parameters[4].Value = model.MeiTi;
            parameters[5].Value = model.Type;
            parameters[6].Value = model.Price;
            parameters[7].Value = model.Marks;
            parameters[8].Value = model.UserId;
            parameters[9].Value = model.Inputime;
            parameters[10].Value = model.Hours;
            parameters[11].Value = model.Driver;
            parameters[12].Value = model.Cents;

            int intResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "Illegal_Update", parameters);

            if (intResult > 0)
                return true;
            else
                return false;
        }
        public bool Delete(int Id)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)};
            parameters[0].Value = Id;
            int intResult = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "Illegal_Delete", parameters);

            if (intResult > 0)
                return true;
            else
                return false;
        }
        public Model.Illegal GetModel(int Id)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)};
            parameters[0].Value = Id;

            Model.Illegal model = new Model.Illegal();
            DataSet ds = SqlHelper.GetDataFormDataSet(CommandType.StoredProcedure, "Illegal_GetModel", parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["Id"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[0]["Id"].ToString());
                }
                model.CarCode = ds.Tables[0].Rows[0]["CarCode"].ToString();
                if (ds.Tables[0].Rows[0]["Time"].ToString() != "")
                {
                    model.Time = DateTime.Parse(ds.Tables[0].Rows[0]["Time"].ToString());
                }
                model.Addr = ds.Tables[0].Rows[0]["Addr"].ToString();
                model.MeiTi = ds.Tables[0].Rows[0]["MeiTi"].ToString();
                model.Type = ds.Tables[0].Rows[0]["Type"].ToString();
                if (ds.Tables[0].Rows[0]["Price"].ToString() != "")
                {
                    model.Price = decimal.Parse(ds.Tables[0].Rows[0]["Price"].ToString());
                }
                model.Marks = ds.Tables[0].Rows[0]["Marks"].ToString();
                if (ds.Tables[0].Rows[0]["UserId"].ToString() != "")
                {
                    model.UserId = int.Parse(ds.Tables[0].Rows[0]["UserId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Inputime"].ToString() != "")
                {
                    model.Inputime = DateTime.Parse(ds.Tables[0].Rows[0]["Inputime"].ToString());
                }
                model.InputTrueName = ds.Tables[0].Rows[0]["TrueName"].ToString();
                model.Hours = ds.Tables[0].Rows[0]["Hours"].ToString();
                model.Driver = ds.Tables[0].Rows[0]["Driver"].ToString();
                model.Cents = ds.Tables[0].Rows[0]["Cents"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Id,CarCode,Time,Addr,MeiTi,Type,Price,Marks,UserId,Inputime,Hours,Driver,Cents ");
            strSql.Append(" FROM Illegal ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return SqlHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 还车结算时，统计违章价格总计
        /// </summary>
        /// <param name="carCode"></param>
        /// <param name="beginTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public decimal GetSumPriceByCarCode(string carCode, DateTime beginTime, DateTime endTime)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@CarCode", SqlDbType.NVarChar,50),
					new SqlParameter("@TimeBegin", SqlDbType.DateTime),
					new SqlParameter("@TimeEnd", SqlDbType.DateTime)
                             };

            parameters[0].Value = carCode;
            parameters[1].Value = beginTime;
            parameters[2].Value = endTime.AddSeconds(1);

            decimal result = 0;
            try
            {
                object obj = SqlHelper.ExecuteScalar(CommandType.StoredProcedure, "Illegal_SumPrice", parameters);
                if (obj.ToString() != "")
                    result = Convert.ToDecimal(obj);
            }
            catch (Exception ex)
            {
            }

            return result;
        }

        /// <summary>
        /// 还车结算时，统计违章的ID
        /// </summary>
        /// <param name="carCode"></param>
        /// <param name="beginTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public DataSet GetKeysByCarCode(string carCode, DateTime beginTime, DateTime endTime)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@CarCode", SqlDbType.NVarChar,50),
					new SqlParameter("@TimeBegin", SqlDbType.DateTime),
					new SqlParameter("@TimeEnd", SqlDbType.DateTime)
                             };

            parameters[0].Value = carCode;
            parameters[1].Value = beginTime;
            parameters[2].Value = endTime.AddSeconds(1);

            DataSet result = null;
            try
            {
                result = SqlHelper.GetDataFormDataSet(CommandType.StoredProcedure, "Illegal_GetAllKeys", parameters);
            }
            catch (Exception ex)
            {
            }

            return result;
        }

        /// <summary>
        /// 还车结算时，更改已经结算的状态
        /// </summary>
        /// <param name="carCode"></param>
        /// <param name="beginTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        public bool GetUpdateStatusByKeysByCarCode(string carCode, DateTime beginTime, DateTime endTime, string ids)
        {
            //SqlParameter[] parameters = {
            //        new SqlParameter("@CarCode", SqlDbType.NVarChar,50),
            //        new SqlParameter("@TimeBegin", SqlDbType.DateTime),
            //        new SqlParameter("@TimeEnd", SqlDbType.DateTime),
            //        new SqlParameter("@IDS", SqlDbType.NVarChar)
            //                 };

            //parameters[0].Value = carCode;
            //parameters[1].Value = beginTime;
            //parameters[2].Value = endTime.AddSeconds(1);
            //parameters[3].Value = ids;

            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Illegal set Status='已结算' ");
            strSql.Append(" where Id in (" + ids + ")  ");
            strSql.Append("and CarCode='" + carCode+"' ");
            strSql.Append("and '" + beginTime + "' <= time and time <= '" + endTime+"' ");

            int intResult = SqlHelper.ExecuteNonQuery(CommandType.Text, strSql.ToString(), null);

            if (intResult > 0)
                return true;
            else
                return false;
        }
    }
}
